IF OBJECT_ID('ssf.job_get_run_status_info') IS NOT NULL
	DROP FUNCTION ssf.job_get_run_status_info
GO

CREATE FUNCTION ssf.job_get_run_status_info (
	@job_id UNIQUEIDENTIFIER
	, @run_datetime DATETIME
)
RETURNS TABLE
AS
/*
<object_header>

<object_summary>
	<purpose><![CDATA[
Return sysjobhistory info for the given job_id and run date/time.
	]]></purpose>
</object_summary>

</object_header>
*/

	RETURN (
		SELECT instance_id
			, job_id
			, [message] AS job_message
			, run_status
			, ssf.job_run_datetime(run_date, run_time) AS run_datetime
			, ssf.job_run_duration_ss(run_duration) AS run_duration_ss
		FROM msdb.dbo.sysjobhistory WITH (NOLOCK)
		WHERE job_id = @job_id
		AND step_id = 0
		AND ssf.job_run_datetime(run_date, run_time) = @run_datetime
	)
GO
